set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
insert overwrite table jms_dm.dm_terminal_city_to_city_sign_dt partition(dt)
-- 末端城市到城市的签收
select 
       end_agent_code,--末端网点所属代理区编码
       max(end_agent_name) as end_agent_name,--末端网点所属代理区名称
       end_provider_id,--末端网点所属省份id
       max(end_provider_name) as end_provider_name,--末端网点所属省份名称
       end_city_id,--末端网点所属城市id
       max(end_city_name) as end_city_name,--末端网点所属城市名称
       pick_agent_code, --寄件代理区code
       max(pick_agent_name) as pick_agent_name, --寄件代理区
       pick_provider_id,  --寄件省份id
       max(pick_provider_name) as pick_provider_name,  --寄件省
       pick_city_id,  --寄件城市id
       max(pick_city_name) as pick_city_name,   --寄件城市
       sum(1) as need_sign_count,--应签收汇总
       sum(case when aging_sign_count_type = 11 then 1 else 0 end) as aging_sign_in_time_network,--时效签收准点-网点签收
       sum(case when aging_sign_count_type = 12 then 1 else 0 end) as aging_sign_in_time_terminal_pdd,--时效签收准点-终端入库(桃花岛认证)
       sum(case when aging_sign_count_type = 13 then 1 else 0 end) as aging_sign_in_time_terminal_other,--时效签收准点-终端入库(其他)
       sum(case when aging_sign_count_type = 14 then 1 else 0 end) as aging_sign_in_time_proxy,--时效签收准点-代理点收入
       sum(case when aging_sign_count_type = 15 then 1 else 0 end) as aging_sign_in_time_transfer,--时效签收准点-转邮
       sum(case when aging_sign_count_type in (11,12,13,14,15) then 1 else 0 end) as aging_sign_in_time_count,--时效签收准点-汇总
     --原始
       sum(case when aging_sign_count_type = 21 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_network,--时效签收延误-网点签收
       sum(case when aging_sign_count_type = 22 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_terminal_pdd,--时效签收延误-终端入库(桃花岛认证)
       sum(case when aging_sign_count_type = 23 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_terminal_other,--时效签收延误-终端入库(其他)
       sum(case when aging_sign_count_type = 24 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_proxy,--时效签收延误-代理点收入
       sum(case when aging_sign_count_type = 25 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_difficult,--时效签收延误-问题件
       sum(case when aging_sign_count_type = 26 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_other,--时效签收延误-其他
      sum(case when aging_sign_count_type = 27 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_transfer,--时效签收延误-转邮
       sum(case when aging_sign_count_type in (21,22,23,24,27) and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_sign_count,--时效签收延误-已签收汇总
       sum(case when aging_sign_count_type in (25,26) and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_nosign_count,--时效签收延误-未签收汇总
       --新增1  10
       sum(case when aging_sign_count_type = 21 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_network,--时效签收延误-网点签收
       sum(case when aging_sign_count_type = 22 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_terminal_pdd,--时效签收延误-终端入库(桃花岛认证)
       sum(case when aging_sign_count_type = 23 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_terminal_other,--时效签收延误-终端入库(其他)
       sum(case when aging_sign_count_type = 24 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_proxy,--时效签收延误-代理点收入
       sum(case when aging_sign_count_type = 25 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_difficult,--时效签收延误-问题件
       sum(case when aging_sign_count_type = 26 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_other,--时效签收延误-其他
       sum(case when aging_sign_count_type = 27 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_transfer,--时效签收延误-转邮
       sum(case when aging_sign_count_type in (21,22,23,24,27) and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_sign_count,--时效签收延误-已签收汇总
       sum(case when aging_sign_count_type in (25,26) and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_nosign_count,--时效签收延误-未签收汇总
      --新增2
       sum(case when aging_sign_count_type = 21 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_network,--时效签收延误-网点签收
       sum(case when aging_sign_count_type = 22 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_terminal_pdd,--时效签收延误-终端入库(桃花岛认证)
       sum(case when aging_sign_count_type = 23 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_terminal_other,--时效签收延误-终端入库(其他)
       sum(case when aging_sign_count_type = 24 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_proxy,--时效签收延误-代理点收入
       sum(case when aging_sign_count_type = 25 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_difficult,--时效签收延误-问题件
       sum(case when aging_sign_count_type = 26 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_other,--时效签收延误-其他
       sum(case when aging_sign_count_type = 27 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_transfer,--时效签收延误-转邮
       sum(case when aging_sign_count_type in (21,22,23,24,27) and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_sign_count,--时效签收延误-已签收汇总
       sum(case when aging_sign_count_type in (25,26) and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_nosign_count,--时效签收延误-未签收汇总
    --结束
       sum(case when is_aging_sign_24 = 1 then 1 else 0 end) as aging_sign_24_count,--时效签收-24点前签收汇总
       sum(case when actual_sign_count_type = 11 then 1 else 0 end) as actual_sign_in_time_network,--实际签收准点-网点签收
       sum(case when actual_sign_count_type = 12 then 1 else 0 end) as actual_sign_in_time_terminal,--实际签收准点-驿站/快递柜
       sum(case when actual_sign_count_type = 13 then 1 else 0 end) as actual_sign_in_time_proxy,--实际签收准点-代理点收入
       sum(case when actual_sign_count_type in (11,12,13) then 1 else 0 end) as actual_sign_in_time_count,--实际签收准点-汇总
       sum(case when actual_sign_count_type = 21 then 1 else 0 end) as actual_sign_over_time_network,--实际签收延误-网点签收
       sum(case when actual_sign_count_type = 22 then 1 else 0 end) as actual_sign_over_time_terminal,--实际签收延误-驿站/快递柜
       sum(case when actual_sign_count_type = 23 then 1 else 0 end) as actual_sign_over_time_proxy,--实际签收延误-代理点收入
       sum(case when actual_sign_count_type = 24 then 1 else 0 end) as actual_sign_over_time_difficult,--实际签收延误-问题件
       sum(case when actual_sign_count_type = 25 then 1 else 0 end) as actual_sign_over_time_other,--实际签收延误-其他
       sum(case when actual_sign_count_type in (21,22,23) then 1 else 0 end) as actual_sign_over_time_sign_count,--实际签收延误-已签收汇总
       sum(case when actual_sign_count_type in (24,25) then 1 else 0 end) as actual_sign_over_time_nosign_count,--实际签收延误-未签收汇总
       sum(case when is_actual_sign_24 = 1 then 1 else 0 end) as actual_sign_24_count,--实际签收-24点前签收汇总
       final_plan_sign_date,--规划签收日期
       sum(case when is_aging_sign_00 = 1 then 1 else 0 end) as aging_sign_00_count,--时效签收-24点前签收汇总
       sum(case when is_aging_sign_22 = 1 then 1 else 0 end) as aging_sign_22_count,--时效签收-24点前签收汇总
       --新增实际签收未维度  22前点汇总
       sum(case when actual_sign_count_type = 11 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_in_time_network,     --22点前实际签收准点-网点签收
       sum(case when actual_sign_count_type = 12 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_in_time_terminal,    --22点前实际签收准点-驿站/快递柜
       sum(case when actual_sign_count_type = 13 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_in_time_proxy,       --22点前实际签收准点-代理点收入
       sum(case when actual_sign_count_type in (11,12,13) and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)  as 22_actual_sign_in_time_count,       --22点前实际签收准点-汇总
       sum(case when actual_sign_count_type = 21 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_network,   --22点前实际签收延误-网点签收
       sum(case when actual_sign_count_type = 22 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_terminal,  --22点前实际签收延误-驿站/快递柜
       sum(case when actual_sign_count_type = 23 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_proxy,     --22点前实际签收延误-代理点收入
       sum(case when actual_sign_count_type = 24 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_difficult, --22点前实际签收延误-问题件
       sum(case when actual_sign_count_type = 25 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_other,     --22点前实际签收延误-其他
       sum(case when actual_sign_count_type in (21,22,23) and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_actual_sign_over_time_sign_count, --22点前实际签收延误-已签收汇总
       sum(case when actual_sign_count_type in (24,25) and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)     as 22_actual_sign_over_time_nosign_count, --22点前实际签收延误-未签收汇总
       --新增实际签收未维度  22-24点汇总
       sum(case when actual_sign_count_type = 11 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_in_time_network,     --22-24点实际签收准点-网点签收
       sum(case when actual_sign_count_type = 12 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_in_time_terminal,    --22-24点实际签收准点-驿站/快递柜
       sum(case when actual_sign_count_type = 13 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_in_time_proxy,       --22-24点实际签收准点-代理点收入
       sum(case when actual_sign_count_type in (11,12,13) and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 24_actual_sign_in_time_count,       --22-24点实际签收准点-汇总
       sum(case when actual_sign_count_type = 21 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_network,   --22-24点实际签收延误-网点签收
       sum(case when actual_sign_count_type = 22 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_terminal,  --22-24点实际签收延误-驿站/快递柜
       sum(case when actual_sign_count_type = 23 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_proxy,     --22-24点实际签收延误-代理点收入
       sum(case when actual_sign_count_type = 24 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_difficult, --22-24点实际签收延误-问题件
       sum(case when actual_sign_count_type = 25 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_other,     --22-24点实际签收延误-其他
       sum(case when actual_sign_count_type in (21,22,23) and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 24_actual_sign_over_time_sign_count,--22-24点实际签收延误-已签收汇总
      sum(case when actual_sign_count_type in (24,25) and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)     as 24_actual_sign_over_time_nosign_count,--22-24实际签收延误-未签收汇总
       --新增实际签收未维度  超24点汇总
       sum(case when actual_sign_count_type = 11  and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 00_actual_sign_in_time_network,     --超24点实际签收准点-网点签收
       sum(case when actual_sign_count_type = 12 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_in_time_terminal,    --超24点实际签收准点-驿站/快递柜
       sum(case when actual_sign_count_type = 13 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_in_time_proxy,       --超24点实际签收准点-代理点收入
       sum(case when actual_sign_count_type in (11,12,13) and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 00_actual_sign_in_time_count,       --超24点实际签收准点-汇总
       sum(case when actual_sign_count_type = 21 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null))  then 1 else 0 end)         as 00_actual_sign_over_time_network,   --超24点实际签收延误-网点签收
       sum(case when actual_sign_count_type = 22 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_terminal,  --超24点实际签收延误-驿站/快递柜
       sum(case when actual_sign_count_type = 23 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_proxy,     --超24点实际签收延误-代理点收入
       sum(case when actual_sign_count_type = 24 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_difficult, --超24点实际签收延误-问题件
       sum(case when actual_sign_count_type = 25 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_other,     --超24点实际签收延误-其他
       sum(case when actual_sign_count_type in (21,22,23) and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 00_actual_sign_over_time_sign_count,--超24点实际签收延误-已签收汇总
       sum(case when actual_sign_count_type in (24,25) and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date  | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)    as 00_actual_sign_over_time_nosign_count,--超24点实际签收延误-未签收汇总
       sum(if( actual_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00'),1,0)) as actual_sign_22_count,   -- 22点签收量
       sum(if( actual_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59'),1,0)) as actual_sign_00_count,   --24点前前签收量
       final_plan_sign_date as dt  -- 分区日期
   from jms_dm.dm_terminal_sign_detail_new_dt t
   where dt between date_add('{{ execution_date  | cst_ds }}', -29) and date_add('{{ execution_date  | cst_ds }}', 0)
   and final_plan_sign_date between date_add('{{ execution_date  | cst_ds }}', -14) and date_add('{{ execution_date  | cst_ds }}', 0)
group by final_plan_sign_date,
         end_agent_code,
         end_provider_id,
         end_city_id,
         pick_city_id,
         pick_provider_id,
         pick_agent_code
distribute by dt, abs(hash(end_city_id)) % 5
 ;